package com.tpc.shuyu.utils;


import com.alibaba.druid.pool.DruidDataSource;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.dbutils.DbUtils;

import java.io.Closeable;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.*;

/**
 * JDBC数据库操作工具类  简化数据库操作
 * @author wxisme
 *
 */
@Slf4j
public class JDBCUtil {

    private static DruidDataSource dataSource = null;

    static {
        try {
            File directory = new File(".");
            String path = directory.getCanonicalPath();

            path = path + "/db.properties";
            Properties props = new Properties();
            FileInputStream in = new FileInputStream(path);
            props.load(in);
            String dbDriver = props.getProperty("dbDriver");
            String dbURL = props.getProperty("dbURL");
            String dbUser = props.getProperty("dbUser");
            String dbPwd = props.getProperty("dbPwd");

            dataSource = new DruidDataSource();
            dataSource.setDriverClassName(dbDriver);
            dataSource.setUrl(dbURL);
            dataSource.setUsername(dbUser);
            dataSource.setPassword(dbPwd);
            dataSource.setMinIdle(5);
            dataSource.setMaxActive(10);
            dataSource.setRemoveAbandoned(true);
            dataSource.setRemoveAbandonedTimeout(30);
            dataSource.setMaxWait(10000);
            dataSource.setInitialSize(5);
//            dataSource.setTestOnBorrow(false);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @SneakyThrows
    public static Connection getConnection()  {
        Connection connection = null;
        connection = dataSource.getConnection();
        return connection;
    }

    @SneakyThrows
    public  static boolean IsOracle(){
        boolean oracleFlag = false;
        Connection  connection = null;
        try {
            connection = JDBCUtil.getConnection();
            oracleFlag =  connection.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle") ? true : false;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtil.close(connection);
        }


        return oracleFlag;
    }
    /**
     * 关闭io资源
     * @param io
     */
    public static void closeFile(Closeable... io) {
        for(Closeable temp : io) {
            if(temp != null) {
                try {
                    temp.close();
                } catch (IOException e) {
                    System.out.println("文件关闭失败");
                    e.printStackTrace();
                }
            }
        }
    }

    //关闭JDBC资源  注意顺序
    public static void close(ResultSet rs, Statement ps, Connection conn) {
        try {
            if(rs!=null){
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(ps!=null){
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(Statement ps,Connection conn){
        try {
            if(ps!=null){
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(Connection conn){
        if(conn!=null){
//                conn.close();
            DbUtils.closeQuietly(conn);
        }
    }

    public static void close(Connection conn, PreparedStatement ps){
        try {

            if(ps!=null){
                ps.close();
            }

            if(conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(CallableStatement call){
        try {
            if(call!=null){
                call.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //获取当前表的列
    public static String getTableColumn(String tableName)  {
        Connection connection = null;
        DatabaseMetaData metadata = null;
        List<String> list = new ArrayList<>();
        try {

            connection = JDBCUtil.getConnection();
            metadata = connection.getMetaData();
            ResultSet resultSet =
                    metadata.getColumns(null, null, tableName.toUpperCase(), null);
            while (resultSet.next()) {
                String name = resultSet.getString("COLUMN_NAME");
//                nameColumn = nameColumn + name.toLowerCase() + ",";
                list.add(name.toLowerCase());
            }
            resultSet.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(connection);
        }
        ;
        return org.apache.commons.lang3.StringUtils.join(list,",");
    }

    //获取列及列类型
    public static Map<String,String> getTableColumnType(String tableName) throws SQLException {
        Connection connection = null;
        DatabaseMetaData metadata = null;
        Map<String,String> map = new HashMap<>();
        try {
            connection = JDBCUtil.getConnection();
            metadata = connection.getMetaData();
            ResultSet resultSet =
                    metadata.getColumns(null, null, tableName.toUpperCase(), null);
            while (resultSet.next()) {
                String name = resultSet.getString("COLUMN_NAME").toLowerCase();
                String nameType = resultSet.getString("TYPE_NAME").toLowerCase();
                map.put(name,nameType);
            }
            resultSet.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.close(connection);
        }
        return map;
    }

    public static Map<String,String> getTableColumnType(Connection connection, String tableName) throws SQLException {
        DatabaseMetaData metadata = null;
        Map<String,String> map = new HashMap<>();
        try {
            File directory = new File(".");
            String path = directory.getCanonicalPath();

            path = path + "/db.properties";
            Properties props = new Properties();
            FileInputStream in = new FileInputStream(path);
            props.load(in);
            String dbUser = props.getProperty("dbUser").toUpperCase();
            metadata = connection.getMetaData();
            ResultSet resultSet =
                    metadata.getColumns(null, null, tableName.toUpperCase(), null);
            while (resultSet.next()) {
                String name = resultSet.getString("COLUMN_NAME").toLowerCase();
                String nameType = resultSet.getString("TYPE_NAME").toLowerCase();
                if(name.toUpperCase().equals("COMMENT")){
                    name = "\""+name + "\"";
                }
                map.put(name,nameType);
            }
            resultSet.close();
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return map;
    }

    /**
     * 获取主键字段
     * @param connection
     * @param tableName
     * @return
     * @throws SQLException
     */
    public static List<String> getTableColumnPK(Connection connection, String tableName) throws SQLException {
        DatabaseMetaData metadata = null;
//        Map<String,String> map = new HashMap<>();
        List<String> list = new ArrayList<>();
        try {
            metadata = connection.getMetaData();
            ResultSet resultSet = metadata.getPrimaryKeys(null, null, tableName.toUpperCase());
            while (resultSet.next()) {
                String columnName = resultSet.getString("COLUMN_NAME");
                list.add(columnName);
            }
            resultSet.close();
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return list;
    }


    public static List<String> getTableColumnNew(String tableName) throws SQLException {
        Connection connection = JDBCUtil.getConnection();
        DatabaseMetaData metadata = null;
        List<String> map = new ArrayList<>();
        try {
            File directory = new File(".");
            String path = directory.getCanonicalPath();

            path = path + "/db.properties";
            Properties props = new Properties();
            FileInputStream in = new FileInputStream(path);
            props.load(in);
            String dbUser = props.getProperty("dbUser").toUpperCase();
            metadata = connection.getMetaData();
            ResultSet resultSet =
                    metadata.getColumns(null, dbUser, tableName.toUpperCase(), null);
            while (resultSet.next()) {
                String name = resultSet.getString("COLUMN_NAME").toLowerCase();
                map.add(name);
            }
            resultSet.close();
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.close(connection);
        }
        return map;
    }



    public static void main(String[] args) throws SQLException {
        String tableName = "presaleappmt";
        Map<String, String> tableColumnType = getTableColumnType(tableName);
        log.info(tableColumnType.toString());
//        Set<String> strings = tableColumnType.keySet();
//        log.info(StringUtils.join(strings,","));



    }
}
